package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Department;
import cn.edu.sdjzu.xg.bysj.domain.School;
import cn.edu.sdjzu.xg.bysj.service.SchoolService;
import lombok.extern.slf4j.Slf4j;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

@Slf4j
public final class DepartmentDao {
    private static DepartmentDao departmentDao = null;

    private DepartmentDao() {
    }

    public static DepartmentDao getInstance() {
        // 静态变量DepartmentDao为空时，才创建DepartmentDao对象。
        // 第二次调用本方法DepartmentDao就不空了，不再创建对象。
        if (departmentDao == null) {
            departmentDao = new DepartmentDao();
        }
        return departmentDao;
    }

    public Collection<Department> findAll(Connection connection,
                                          List conditions_json,
                                          Pagination pagination)
            throws SQLException {

        Collection<Department> desiredDepartments = new HashSet<Department>();
        //准备预编译的语句
        int totalNum = DepartmentDao.getInstance().count(connection);
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM department");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditions_json != null) {
            String whereClause = Condition.toWhereClause(conditions_json);
            sql_select.append(whereClause);
        }
        if (pagination != null) {
            sql_select.append(pagination.toLimitClause(totalNum));
        }
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();
        //遍历resultSet
        while (resultSet_select.next()) {
            int id = resultSet_select.getInt("id");
            String description = resultSet_select.getString("description");
            String no = resultSet_select.getString("no");
            String remarks = resultSet_select.getString("remarks");
            Department desiredDepartment = new Department(description, no, remarks);
            desiredDepartment.setId(id);
            desiredDepartments.add(desiredDepartment);

        }
        JdbcHelper.close(resultSet_select, pstmt_select);
        return desiredDepartments;
    }

    private int count(Connection conn) throws SQLException {
        String countDepartment_sql = "select count(*)  from department";
        PreparedStatement pstmt = conn.prepareStatement(countDepartment_sql);
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        int num = resultSet.getInt("count(*)");
        log.debug("", num);
        JdbcHelper.close(resultSet, pstmt);
        return num;

    }

    public static Department find(Integer id, Connection connection) throws SQLException {
        String department_sql = "select * from Department where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(department_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //根据id查找Department
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        //根据id查找结果创建Department对象
        Department department = new Department(
                id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"),
                SchoolService.getInstance().find(resultSet.getInt("school_id")));
        //关闭
        JdbcHelper.close(pstmt, connection);
        return department;
    }

    public boolean update(Department department, Connection connection) throws SQLException {
        String updateDepartment_sql = "UPDATE Department SET no = ?,description = ?,remarks = ?,school_id = ? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateDepartment_sql);
        //为预编译参数赋值
        pstmt.setString(1, department.getNo());
        pstmt.setString(2, department.getDescription());
        pstmt.setString(3, department.getRemarks());
        pstmt.setInt(4, department.getSchool().getId());
        pstmt.setInt(5, department.getId());

        //修改Department 并判断是否修改成功
        return pstmt.executeUpdate() > 0;
    }

    public boolean add(Department department, Connection connection) throws SQLException {

        String addDepartment_sql = "INSERT INTO Department(description,no,remarks,school_id)" +
                " VALUES" + "(?,?,?,?)";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addDepartment_sql);
        //为预编译参数赋值
        pstmt.setString(1, department.getDescription());
        pstmt.setString(2, department.getNo());
        pstmt.setString(3, department.getRemarks());
        pstmt.setInt(4, department.getSchool().getId());
        //添加Department 并判断是否添加成功
        return pstmt.executeUpdate() > 0;
    }

    public boolean delete(Department department, Connection connection) throws SQLException {

        String deleteDepartment_sql = "delete from Department where no = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteDepartment_sql);
        //为预编译参数赋值
        pstmt.setString(1, department.getNo());
        //根据id删除Department 并判断是否删除成功
        return pstmt.executeUpdate() > 0;
    }

    public int countAll(School school, Connection connection) throws SQLException {
        String countDepartment_sql = "SELECT COUNT(id) as cnt_by_school_id from department where school_id=?";
        PreparedStatement pstmt = connection.prepareStatement(countDepartment_sql);
        pstmt.setInt(1, school.getId());
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        int count = resultSet.getInt("cnt_by_school_id");
        return count;
    }

}

